Imports ADOConnection
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient

Public Class CHopDong
    Protected _hopdong_id As Integer
    Protected _sohopdong As String
    Protected _ngaykyhd As Date
    Protected _ngayketthuc As Date

#Region "Property"
    Public Property HOPDONG_ID() As Integer
        Get
            Return _hopdong_id
        End Get
        Set(ByVal value As Integer)
            _hopdong_id = value
        End Set
    End Property

    Public Property SOHOPDONG() As String
        Get
            Return _sohopdong
        End Get
        Set(ByVal value As String)
            _sohopdong = value
        End Set
    End Property

    Public Property NGAYKYHD() As Date
        Get
            Return _ngaykyhd
        End Get
        Set(ByVal value As Date)
            _ngaykyhd = value
        End Set
    End Property

    Public Property NGAYKETTHUC() As Date
        Get
            Return _ngayketthuc
        End Get
        Set(ByVal value As Date)
            _ngayketthuc = value
        End Set
    End Property
#End Region

    Public Function Insert() As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim objStringBuilder As New StringBuilder(250)
        With objStringBuilder
            .Append("INSERT INTO tblHOPDONG(" & _
                    "sohopdong, ngaykyhd, ngayketthuc) VALUES(")
            .Append(objDBUtility.GetParamPlaceHolder("sohopdong", True))
            .Append(objDBUtility.GetParamPlaceHolder("ngaykyhd", True))
            .Append(objDBUtility.GetParamPlaceHolder("ngayketthuc", False))
            .Append(")")
        End With

        Dim sInsert As String = objStringBuilder.ToString

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("sohopdong", DbType.String, _sohopdong, _sohopdong.Length))
                .Parameters.Add(objDBUtility.CreateParameter("ngaykyhd", DbType.Date, GetDateParam(_ngaykyhd), 0))
                .Parameters.Add(objDBUtility.CreateParameter("ngayketthuc", DbType.Date, GetDateParam(_ngayketthuc), 0))
            End With
            _hopdong_id = objDBUtility.InsertAndReturnSerialKey(objCommand, "hopdong_id", "tblHOPDONG")
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return _hopdong_id
    End Function

    Public Function Update() As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "UPDATE tblHOPDONG SET"
        strSQL &= " SOHOPDONG = " & objDBUtility.GetParamPlaceHolder("SOHOPDONG", True)
        strSQL &= " NGAYKYHD = " & objDBUtility.GetParamPlaceHolder("NGAYKYHD", True)
        strSQL &= " NGAYKETTHUC = " & objDBUtility.GetParamPlaceHolder("NGAYKETTHUC", False)
        strSQL &= " WHERE HOPDONG_ID = " & objDBUtility.GetParamPlaceHolder("HOPDONG_ID", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("sohopdong", DbType.String, _sohopdong, _sohopdong.Length))
                .Parameters.Add(objDBUtility.CreateParameter("ngaykyhd", DbType.Date, GetDateParam(_ngaykyhd), 0))
                .Parameters.Add(objDBUtility.CreateParameter("ngayketthuc", DbType.Date, GetDateParam(_ngayketthuc), 0))
                .Parameters.Add(objDBUtility.CreateParameter("hopdong_id", DbType.Decimal, _hopdong_id, 0))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function
End Class

Public Class CHopDongs
    Public Shared Function IsExist(ByVal loai As enumDoiTuong, ByVal pSoHD As String) As Boolean
        Dim strSQL As String = "select * from tblHOPDONG where sohopdong like N'" + pSoHD + "'"
        Dim objReader As SqlDataReader
        objReader = BaseDB.ExecSql_DataReader(strSQL)
        If objReader.Read() Then
            objReader.Close()
            Return True
        End If
        Return False
    End Function

    Public Shared Function GetSoHDByHopDong_ID(ByVal mHopDong_ID As Integer) As String
        Dim strSQL As String = "select sohopdong from tblHopDong where hopdong_id = " + mHopDong_ID.ToString()
        Dim sohopdong As String = ""
        Try
            sohopdong = CStr(BaseDB.ExecSql_DataValue(strSQL))
        Catch ex As System.Exception
            Throw ex
        End Try
        Return sohopdong
    End Function

    Public Shared Function GetHopDong_IDBySoHD(ByVal mSoHD As String) As Integer
        Dim strSQL As String = "select hopdong_id from tblHopDong where sohopdong like N'" + mSoHD.ToString() + "'"
        Dim hopdong_id As Integer = 0
        Try
            hopdong_id = CInt(BaseDB.ExecSql_DataValue(strSQL))
        Catch ex As System.Exception
            Throw ex
        End Try
        Return hopdong_id
    End Function

    Public Shared Function GetHopDongBySoHD(ByVal pSoHD As String) As CHopDong
        Dim strSQL As String = "select * from tblHopDong where sohopdong = N'" + pSoHD + "'"
        Dim objReader As SqlDataReader = Nothing
        Dim obj As New CHopDong
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            If objReader.Read() Then
                With obj
                    .HOPDONG_ID = objReader.GetInt32(0)
                    .SOHOPDONG = objReader.GetString(1)
                    .NGAYKYHD = objReader.GetDateTime(2)
                    Try
                        .NGAYKETTHUC = objReader.GetDateTime(3)
                    Catch ex As Exception
                        .NGAYKETTHUC = DateNull()
                    End Try
                End With
            End If
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
        End Try
        Return obj
    End Function

    Public Shared Function GetHopDongByMa(ByVal mHOPDONG_ID As Integer) As CHopDong
        Dim strSQL As String = "select * from tblHopDong where hopdong_id = " + mHOPDONG_ID.ToString()
        Dim objReader As SqlDataReader = Nothing
        Dim obj As New CHopDong
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            If objReader.Read() Then
                With obj
                    .HOPDONG_ID = objReader.GetInt32(0)
                    .SOHOPDONG = objReader.GetString(1)
                    .NGAYKYHD = objReader.GetDateTime(2)
                    Try
                        .NGAYKETTHUC = objReader.GetDateTime(3)
                    Catch ex As Exception
                        .NGAYKETTHUC = DateNull()
                    End Try
                End With
            End If
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
        End Try
        Return obj
    End Function

    Public Shared Function GetHopDongList(Optional ByVal strDK As String = "") As ArrayList
        Dim aryItems As New ArrayList
        Dim strSQL As String
        Dim strWHERE As String = ""

        If strDK.Length > 0 Then
            strWHERE = " WHERE " & strDK & ""
        End If

        strSQL = "SELECT * FROM tblHopDong"
        strSQL &= strWHERE
        strSQL &= " order by sohopdong asc"
        'strSQL &= " ORDER BY ngaykyhd desc"
        Dim objReader As SqlClient.SqlDataReader = Nothing
        Dim objItem As CHopDong
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            While objReader.Read
                objItem = New CHopDong
                With objItem
                    .HOPDONG_ID = objReader.GetInt32(0)
                    .SOHOPDONG = objReader.GetString(1)
                    .NGAYKYHD = objReader.GetDateTime(2)
                    Try
                        .NGAYKETTHUC = objReader.GetDateTime(3)
                    Catch ex As Exception
                        .NGAYKETTHUC = DateNull()
                    End Try
                End With
                aryItems.Add(objItem)
            End While
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
        End Try
        Return aryItems
    End Function

    '    Public Shared Function GetHopDongListBySql(Optional ByVal strSQL As String = "") As ArrayList
    '        Dim aryItems As New ArrayList
    '        Dim objReader As SqlClient.SqlDataReader = Nothing
    '        Dim objItem As CHopDong
    '        Try
    '            objReader = BaseDB.ExecSql_DataReader(strSQL)
    '            While objReader.Read
    '                objItem = New CHopDong
    '                With objItem
    '                    .HOPDONG_ID = objReader.GetInt32(0)
    '                    .SOHOPDONG = objReader.GetString(1)
    '                    .NGAYKYHD = objReader.GetDateTime(2)
    '                    Try
    '                        .NGAYKETTHUC = objReader.GetDateTime(3)
    '                    Catch ex As Exception
    '                        .NGAYKETTHUC = DateNull()
    '                    End Try
    '                End With
    '                aryItems.Add(objItem)
    '            End While
    '        Catch ex As System.Exception
    '            Throw ex
    '        Finally
    '            If Not IsNothing(objReader) Then
    '                objReader.Close()
    '            End If
    '        End Try
    '        Return aryItems
    '    End Function
End Class
